{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/zheyuanzhang/anaconda3/envs/FRS/lib/python3.8/site-packages/tqdm/auto.py:21: TqdmWarning: IProgress not found. Please update jupyter and ipywidgets. See https://ipywidgets.readthedocs.io/en/stable/user_install.html\n",
      "  from .autonotebook import tqdm as notebook_tqdm\n"
     ]
    }
   ],
   "source": [
    "from utils import *\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# User Tagging"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "## 1. Demographic Info"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "years = ['0304', '0506', '0708', '0910', '1112', '1314', '1516', '1718', '1720']\n",
    "year_char = 'C'\n",
    "type_demo = 'demographic'\n",
    "df_demo = concat_data_across_years(type_demo, 'DEMO', years, year_char)\n",
    "#df_demo['years'] = df_demo['years'].astype(object)\n",
    "\n",
    "# Select the wanted columns. Make changes here if needed in the future.\n",
    "df_demo = df_demo[['SEQN', 'RIAGENDR', 'RIDAGEYR', 'RIDRETH1', 'INDHHINC', 'DMDEDUC2', 'WTINT2YR',\n",
    "                       'WTMEC2YR', 'WTINTPRP', 'WTMECPRP', 'years']]\n",
    "df_demo = df_demo.fillna(0)\n",
    "df_demo[['SEQN', 'RIAGENDR', 'RIDAGEYR', 'RIDRETH1', 'INDHHINC', 'DMDEDUC2']] = \\\n",
    "    df_demo[['SEQN', 'RIAGENDR', 'RIDAGEYR', 'RIDRETH1', 'INDHHINC', 'DMDEDUC2']].astype(int)\n",
    "\n",
    "# Unify the weight columns and make column names readable.\n",
    "df_demo['weight_interview'] = np.where(df_demo['WTINT2YR'] == -1, df_demo['WTINTPRP'], df_demo['WTINT2YR'])\n",
    "df_demo['weight_mec'] = np.where(df_demo['WTMEC2YR'] == -1, df_demo['WTMECPRP'], df_demo['WTMEC2YR'])\n",
    "df_demo.drop(['WTINT2YR','WTMEC2YR', 'WTINTPRP', 'WTMECPRP'], axis=1, inplace=True)\n",
    "df_demo = df_demo.rename(columns={'RIAGENDR': 'gender', 'RIDAGEYR': 'age', 'RIDRETH1': 'race', 'DMDEDUC2': 'education',\n",
    "                                  'INDHHINC': 'household_income'})\n",
    "df_demo['SEQN'] = df_demo['SEQN'].astype(str)\n",
    "df_demo = df_demo.set_index('SEQN')\n",
    "\n",
    "# Drop the weight colummns for now.\n",
    "df_demo.drop(['weight_interview', 'weight_mec'], axis=1, inplace=True)\n",
    "\n",
    "# Transform ages to age groups\n",
    "bins = [-1, 10, 20, 30, 40, 50, 60, 100]\n",
    "labels = [1, 2, 3, 4, 5, 6, 7]\n",
    "\n",
    "# Create a new column for age groups\n",
    "df_demo['age_group'] = pd.cut(df_demo['age'], bins=bins, labels=labels, right=True).astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>gender</th>\n",
       "      <th>age</th>\n",
       "      <th>race</th>\n",
       "      <th>household_income</th>\n",
       "      <th>education</th>\n",
       "      <th>years</th>\n",
       "      <th>age_group</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SEQN</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>21005</th>\n",
       "      <td>1</td>\n",
       "      <td>19</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0304</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21006</th>\n",
       "      <td>2</td>\n",
       "      <td>16</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>0304</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21007</th>\n",
       "      <td>2</td>\n",
       "      <td>14</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0304</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21008</th>\n",
       "      <td>1</td>\n",
       "      <td>17</td>\n",
       "      <td>4</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>0304</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21009</th>\n",
       "      <td>1</td>\n",
       "      <td>55</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>0304</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124818</th>\n",
       "      <td>1</td>\n",
       "      <td>40</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>1720</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124819</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1720</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124820</th>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1720</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124821</th>\n",
       "      <td>1</td>\n",
       "      <td>63</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1720</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124822</th>\n",
       "      <td>1</td>\n",
       "      <td>74</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1720</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>95872 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        gender  age  race  household_income  education years  age_group\n",
       "SEQN                                                                   \n",
       "21005        1   19     4                 6          0  0304          2\n",
       "21006        2   16     4                 7          0  0304          2\n",
       "21007        2   14     3                 0          0  0304          2\n",
       "21008        1   17     4                11          0  0304          2\n",
       "21009        1   55     3                 8          3  0304          6\n",
       "...        ...  ...   ...               ...        ...   ...        ...\n",
       "124818       1   40     4                 0          5  1720          4\n",
       "124819       1    2     4                 0          0  1720          1\n",
       "124820       2    7     3                 0          0  1720          1\n",
       "124821       1   63     4                 0          2  1720          7\n",
       "124822       1   74     2                 0          3  1720          7\n",
       "\n",
       "[95872 rows x 7 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_demo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Generate all tags and initialize them with 0. Following merging will update the tags. \n",
    "column_tag = [\n",
    "    'low_calorie', 'high_calorie', 'low_carb', 'low_protein', 'high_protein' , 'low_saturated_fat', 'low_sugar', 'low_cholesterol', 'high_fiber',\n",
    "    'low_sodium', 'high_potassium', 'high_iron', 'high_calcium', 'high_vitamin_d', 'high_vitamin_c', 'low_phosphorus', 'high_folate_acid', 'high_vitamin_b12'\n",
    "]\n",
    "df_demo[column_tag] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "gender               0\n",
       "age                  0\n",
       "race                 0\n",
       "household_income     0\n",
       "education            0\n",
       "years                0\n",
       "age_group            0\n",
       "low_calorie          0\n",
       "high_calorie         0\n",
       "low_carb             0\n",
       "low_protein          0\n",
       "high_protein         0\n",
       "low_saturated_fat    0\n",
       "low_sugar            0\n",
       "low_cholesterol      0\n",
       "high_fiber           0\n",
       "low_sodium           0\n",
       "high_potassium       0\n",
       "high_iron            0\n",
       "high_calcium         0\n",
       "high_vitamin_d       0\n",
       "high_vitamin_c       0\n",
       "low_phosphorus       0\n",
       "high_folate_acid     0\n",
       "high_vitamin_b12     0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# There is no missing value in the dataset.\n",
    "df_demo.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Special Diet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "type_dietary = 'dietary'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_TOT1 = concat_data_across_years(type_dietary, 'DR1TOT', years, year_char)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "columns_diet = [\"DRQSDT1\", \"DRQSDT2\", \"DRQSDT3\", \"DRQSDT4\", \"DRQSDT7\", \"DRQSDT8\", \"DRQSDT9\", \"DRQSDT10\", \"DRQSDT12\"]\n",
    "mappings_diet = {\n",
    "    \"DRQSDT1\": \"Weight loss/Low calorie diet\", \"DRQSDT2\": \"Low fat/Low cholesterol diet\",\n",
    "    \"DRQSDT3\": \"Low salt/Low sodium diet\", \"DRQSDT4\": \"Sugar free/Low sugar diet\", \"DRQSDT7\": \"Diabetic diet\",\n",
    "    \"DRQSDT8\": \"Weight gain/Muscle building diet\", \"DRQSDT9\": \"Low carbohydrate diet\", \"DRQSDT10\": \"High protein diet\",\n",
    "    \"DRQSDT12\": \"Renal/Kidney diet\"\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_diet = df_TOT1[[\"SEQN\"] + columns_diet]\n",
    "df_diet = df_diet.fillna(0)\n",
    "df_diet.rename(columns=mappings_diet, inplace=True)\n",
    "df_diet = df_diet.astype(int)\n",
    "df_diet['SEQN'] = df_diet['SEQN'].astype(str)\n",
    "df_diet = df_diet.set_index('SEQN')\n",
    "# Convert all integers to 1s if a user is taking a diet\n",
    "df_diet = (df_diet != 0).astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Weight loss/Low calorie diet        4659\n",
       "Low fat/Low cholesterol diet        1170\n",
       "Low salt/Low sodium diet            1011\n",
       "Sugar free/Low sugar diet            413\n",
       "Diabetic diet                       1361\n",
       "Weight gain/Muscle building diet     270\n",
       "Low carbohydrate diet                487\n",
       "High protein diet                    146\n",
       "Renal/Kidney diet                     59\n",
       "dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check general statistics\n",
    "df_diet.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Keep the records if there is at least one diet\n",
    "df_diet = df_diet.loc[~(df_diet == 0).all(axis=1)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    7275\n",
       "2     830\n",
       "3     156\n",
       "4      33\n",
       "5       3\n",
       "6       2\n",
       "7       2\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Some users are taking multiple diets. We need to carefully combine them when using. \n",
    "df_diet.sum(axis=1).value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "columns_diet = df_diet.columns\n",
    "# Convert the diets to health tags \n",
    "df_diet['low_calorie'] = df_diet['Weight loss/Low calorie diet']\n",
    "df_diet['low_saturated_fat'] = df_diet['Low fat/Low cholesterol diet']\n",
    "df_diet['low_sodium'] = df_diet['Low salt/Low sodium diet']\n",
    "df_diet['low_sugar'] = df_diet['Sugar free/Low sugar diet']\n",
    "df_diet['low_sugar'] = df_diet['Diabetic diet'] | df_diet['low_sugar']\n",
    "df_diet['high_fiber'] = df_diet['Diabetic diet']\n",
    "df_diet['low_carb'] = df_diet['Low carbohydrate diet']\n",
    "df_diet['high_protein'] = df_diet['High protein diet']\n",
    "df_diet['low_protein'] = df_diet['Renal/Kidney diet']\n",
    "df_diet['low_sodium'] = df_diet['Renal/Kidney diet'] | df_diet['low_sodium']\n",
    "df_diet['low_phosphorus'] = df_diet['Renal/Kidney diet']\n",
    "df_diet['high_calorie'] = df_diet['Weight gain/Muscle building diet']\n",
    "df_diet['high_protein'] = df_diet['Weight gain/Muscle building diet'] | df_diet['high_protein']\n",
    "df_diet.drop(columns_diet, axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Combine the health tags for each user\n",
    "df_diet = df_diet.groupby('SEQN').max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "low_calorie          4659\n",
       "low_saturated_fat    1170\n",
       "low_sodium           1063\n",
       "low_sugar            1729\n",
       "high_fiber           1361\n",
       "low_carb              487\n",
       "high_protein          408\n",
       "low_protein            59\n",
       "low_phosphorus         59\n",
       "high_calorie          270\n",
       "dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_diet.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "def merge_with_or(df1, df2):\n",
    "    \"\"\"\n",
    "    Merges two DataFrames on a specified key(s) and combines shared columns using an 'OR' relation.\n",
    "    \n",
    "    Parameters:\n",
    "    - df1, df2: DataFrames to be merged.\n",
    "    \n",
    "    Returns:\n",
    "    - A merged DataFrame with combined shared columns using an 'OR' logic.\n",
    "    \"\"\"\n",
    "    # Merge the DataFrames\n",
    "    merged_df = pd.merge(df1, df2, left_index=True, right_index=True, how='left', suffixes=('_df1', '_df2'))\n",
    "    \n",
    "    # Find shared columns, excluding the key(s) used for merging\n",
    "    shared_columns = set(df1.columns) & set(df2.columns)\n",
    "    \n",
    "    for col in shared_columns:\n",
    "        col_df1 = f'{col}_df1'\n",
    "        col_df2 = f'{col}_df2'\n",
    "\n",
    "        # Apply 'OR' operation for the shared column and assign it to the merged DataFrame\n",
    "        # Only df2 contains NaN values, so we need to fill them with False before converting to int\n",
    "        merged_df[col] = (merged_df[col_df1] | merged_df[col_df2].fillna(False).astype(int))\n",
    "        \n",
    "        # Drop the original columns from the merge\n",
    "        merged_df.drop(columns=[col_df1, col_df2], inplace=True)\n",
    "    \n",
    "    return merged_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_main = merge_with_or(df_demo, df_diet)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Medical Info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "User Health Status Tagging\n",
    "\"\"\"\n",
    "\n",
    "def tag_BMI_waist_circumference(row):\n",
    "    underweight_BMI, overweight_BMI = 18.5, 25\n",
    "\n",
    "    waist_threshold_male, waist_threshold_female = 102, 88\n",
    "    waist_threshold = waist_threshold_male if row['gender'] == 1 else waist_threshold_female\n",
    "\n",
    "    high_calories, low_calories = 0, 0\n",
    "    if row['BMXBMI'] < underweight_BMI:\n",
    "        high_calories = 1\n",
    "    \n",
    "    # In practice, either metrics can inidicate overweight. But for confidence, we use \"and\" here. \n",
    "    \n",
    "    if row['BMXBMI'] >= overweight_BMI and row['BMXWAIST'] >= waist_threshold:\n",
    "        low_calories = 1\n",
    "\n",
    "    # this rarely happens, but it means the visceral fat is high, so the user still need low calories food.\n",
    "    if high_calories == 1 and low_calories == 1:\n",
    "        high_calories = 0\n",
    "\n",
    "    return high_calories, low_calories\n",
    "\n",
    "\n",
    "def tag_blood_pressure(row):\n",
    "    high_systolic_threshold, high_diastolic_threshold = 130, 80\n",
    "    low_sodium, high_potassium = 0, 0\n",
    "\n",
    "    # Check if the blood pressure is above the thresholds\n",
    "    if row['Average_Systolic'] >= high_systolic_threshold or row['Average_Diastolic'] >= high_diastolic_threshold:\n",
    "        low_sodium = 1\n",
    "        high_potassium = 1\n",
    "\n",
    "    return low_sodium, high_potassium"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.1. BMI & Waist Circumference"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "type_table = 'examination'\n",
    "df_BMI = concat_data_across_years(type_table, 'BMX', years, year_char)\n",
    "df_BMI['SEQN'] = df_BMI['SEQN'].astype(int).astype(str)\n",
    "df_BMI = df_BMI[['SEQN','BMXBMI', 'BMXWAIST']].copy()\n",
    "df_BMI = df_BMI.merge(df_main, on='SEQN', how='left')\n",
    "df_BMI[['high_calorie', 'low_calorie']] = df_BMI.apply(lambda row: tag_BMI_waist_circumference(row), axis=1, result_type='expand')\n",
    "df_BMI.set_index('SEQN', inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "high_calorie      899\n",
       "low_calorie     26906\n",
       "dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Take a look here for now. Later we will only pay attention the adults.\n",
    "df_BMI.loc[df_BMI['age'] > 18][['high_calorie', 'low_calorie']].sum()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_main = merge_with_or(df_main, df_BMI[['high_calorie', 'low_calorie']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.2. Blood Pressure"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "years = ['0304', '0506', '0708', '0910', '1112', '1314', '1516', '1718']\n",
    "df_BP = concat_data_across_years(type_table, 'BPX', years, year_char)\n",
    "df_BP['Average_Systolic'] = df_BP[['BPXSY1', 'BPXSY2', 'BPXSY3', 'BPXSY4']].mean(axis=1, skipna=True)\n",
    "df_BP['Average_Diastolic'] = df_BP[['BPXDI1', 'BPXDI2', 'BPXDI3', 'BPXDI4']].mean(axis=1, skipna=True)\n",
    "\n",
    "df_BP_O = concat_data_across_years(type_table, 'BPXO', ['1720'], year_char)\n",
    "df_BP_O['Average_Systolic'] = df_BP_O[['BPXOSY1', 'BPXOSY2', 'BPXOSY3']].mean(axis=1, skipna=True)\n",
    "df_BP_O['Average_Diastolic'] = df_BP_O[['BPXODI1', 'BPXODI2', 'BPXODI3']].mean(axis=1, skipna=True)\n",
    "\n",
    "df_BP_concat = pd.concat([df_BP[['SEQN', 'Average_Systolic', 'Average_Diastolic']], df_BP_O[['SEQN', 'Average_Systolic', 'Average_Diastolic']]])\n",
    "df_BP_concat['SEQN'] = df_BP_concat['SEQN'].astype(int).astype(str)\n",
    "\n",
    "df_BP_concat[['low_sodium', 'high_potassium']] = df_BP_concat.apply(tag_blood_pressure, axis=1, result_type='expand')\n",
    "df_BP_concat.set_index('SEQN', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_main = merge_with_or(df_main, df_BP_concat[['low_sodium', 'high_potassium']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.3. Low-Density Lipoprotein"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "type_table = 'laboratory'\n",
    "year_char = 'D'\n",
    "years = ['0506', '0708', '0910', '1112', '1314', '1516']\n",
    "df_LDL = concat_data_across_years(type_table, 'TRIGLY', years, year_char)\n",
    "\n",
    "years = ['1718', '1720']\n",
    "year_char = 'J'\n",
    "type_table = 'laboratory'\n",
    "df_LDL_2 = concat_data_across_years(type_table, 'TRIGLY', years, year_char)\n",
    "\n",
    "df_LDL_3 = concat_data_across_years(type_table, 'L13AM', ['0304'], 'C')\n",
    "\n",
    "df_LDL = pd.concat([df_LDL[['SEQN', 'LBDLDLSI']], df_LDL_2[['SEQN', 'LBDLDLSI']], df_LDL_3[['SEQN', 'LBDLDLSI']]])\n",
    "df_LDL['SEQN'] = df_LDL['SEQN'].astype(int).astype(str)\n",
    "\n",
    "def tag_LDL(row):\n",
    "    threshold = 3.3\n",
    "    low_cholesterol, high_fiber, low_saturated_fat = 0, 0, 0\n",
    "    if row['LBDLDLSI'] and row['LBDLDLSI'] > threshold:\n",
    "        low_cholesterol, high_fiber, low_saturated_fat = 1, 1, 1\n",
    "\n",
    "    return low_cholesterol, high_fiber, low_saturated_fat\n",
    "\n",
    "df_LDL[['low_cholesterol', 'high_fiber', 'low_saturated_fat']] = df_LDL.apply(tag_LDL, axis=1, result_type='expand')\n",
    "df_LDL.set_index('SEQN', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_main = merge_with_or(df_main, df_LDL[['low_cholesterol', 'high_fiber', 'low_saturated_fat']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.4. Blood Urea Nitrogen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "type_table = 'laboratory'\n",
    "df_SBP = concat_data_across_years(type_table, 'BIOPRO',\n",
    "                                 ['0506', '0708', '0910', '1112', '1314', '1516', '1718', '1720'], 'D')\n",
    "df_temp = concat_data_across_years(type_table, 'L40',\n",
    "                                 ['0304'], 'C')\n",
    "df_SBP = pd.concat([df_SBP, df_temp])[['SEQN', 'LBDSBUSI']]\n",
    "df_SBP['SEQN'] = df_SBP['SEQN'].astype(int).astype(str)\n",
    "\n",
    "def tag_protein(row):\n",
    "    return 1 if row['LBDSBUSI'] >= 7.1 else 0\n",
    "\n",
    "df_SBP['low_protein'] = df_SBP.apply(tag_protein, axis=1, result_type='expand')\n",
    "df_SBP.set_index('SEQN', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_main = merge_with_or(df_main, df_SBP[['low_protein']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.5. Opioid Misuse"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "type_questionnaire = 'questionnaire'\n",
    "# No specific illicit drug info in 03-04 data. No such table in 17-20 data.\n",
    "df_DU = concat_data_across_years(type_questionnaire, 'DUQ',\n",
    "                                 ['0506', '0708', '0910', '1112', '1314', '1516', '1718'], 'D')\n",
    "df_DU = df_DU.loc[df_DU['DUQ290'] == 1]\n",
    "df_DU = df_DU[['SEQN', 'DUQ270U', 'DUQ350U', 'DUQ300', 'DUQ310Q', 'DUQ310U']]\n",
    "df_DU = df_DU.fillna(-1)\n",
    "df_DU = df_DU.astype(int)\n",
    "\"\"\"\n",
    "# We only care about those who have used heroin at least once.\n",
    "# If within a year, the user has been using any illicit drugs (heroin, meth, cocaine),\n",
    "# we identify the user as an active user.\n",
    "# Otherwise, we consider this user a recovered user.\n",
    "\n",
    "# To be specific, -1 means missing, 4 means it has been years that a user haven't used a drug.\n",
    "# If there is a value that is neither -1, nor 4 in any of the three columns, the user is an active user.\n",
    "\n",
    "# We label the active user as 1 and recovered user as 2.\n",
    "# And the rest of the users who hasn't even used heroin or other opioid prescription drugs as 0: non-opioid-user\n",
    "\"\"\"\n",
    "df_DU['active_user'] = np.where((df_DU['DUQ270U'].isin([4, -1]) == False) |\n",
    "                                 (df_DU['DUQ350U'].isin([4, -1]) == False) |\n",
    "                                 (df_DU['DUQ310U'].isin([4, -1]) == False), 1, 2)\n",
    "df_DU = df_DU.rename(columns={'DUQ300': 'age_first_use_heroin', 'DUQ310U': 'last_time_unit_used_heroin',\n",
    "                              'DUQ310Q': 'last_time_used_heroin', 'DUQ270U': 'last_time_unit_used_cocaine',\n",
    "                              'DUQ350U': 'last_time_unit_used_meth'})\n",
    "df_DU['SEQN'] = df_DU['SEQN'].astype(str)\n",
    "df_DU = df_DU.set_index('SEQN')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "years = ['0304', '0506', '0708', '0910', '1112', '1314', '1516', '1718', '1720']\n",
    "year_char = 'C'\n",
    "df_PM = concat_data_across_years(type_questionnaire, 'RXQ_RX', years, year_char)\n",
    "df_PM = df_PM[df_PM['RXDUSE']==1]\n",
    "df_PM_1 = df_PM.loc[(df_PM['RXDRSC1'] == 'F11.2') | (df_PM['RXDRSC1'] == 'F11.23')]\n",
    "\n",
    "drugs = pd.read_sas('../data/RXQ_DRUG.xpt', encoding='ISO-8859-1')\n",
    "drug_60 = drugs[(drugs['RXDDCI1A'] == 57) & (drugs['RXDDCI1B'] == 58) & (drugs['RXDDCI1C'] == 60)]\n",
    "drug_191 = drugs[(drugs['RXDDCI1A'] == 57) & (drugs['RXDDCI1B'] == 58) & (drugs['RXDDCI1C'] == 191)]\n",
    "drug = pd.concat([drug_60, drug_191])\n",
    "drug_id = set(drug['RXDDRGID'].tolist())\n",
    "df_PM  = df_PM [df_PM ['RXDDRGID'].isin(drug_id)]\n",
    "df_PM = pd.concat([df_PM, df_PM_1])\n",
    "df_PM = df_PM.drop_duplicates()\n",
    "\n",
    "df_PM = df_PM[['SEQN', 'RXDDRUG', 'RXDDRGID', 'RXDDAYS']]\n",
    "df_PM = df_PM[df_PM['RXDDAYS'] > 90]\n",
    "df_PM[['SEQN', 'RXDDAYS']] = df_PM[['SEQN', 'RXDDAYS']].astype(int)\n",
    "df_PM['SEQN'] = df_PM['SEQN'].astype(str)\n",
    "\n",
    "\"\"\"\n",
    "# We define the long term opioid users as those how have taken opioid prescriptions over 90 days.\n",
    "\n",
    "# Note that each user can take multiple opioid prescriptions.\n",
    "\n",
    "# In an earlier study for tracking long term opioid users,\n",
    "# The author excluded medications containing buprenorphine since they are used to treat use disorder.\n",
    "# However, we find multiple cases that this medicine used to treat opioid dependence.\n",
    "# So technically this also implies the user is a long term opioid user.\n",
    "\"\"\"\n",
    "df_PM = df_PM.rename(columns={'RXDDRUG': 'drug_name', 'RXDDRGID': 'drug_id', 'RXDDAYS': 'days_using'})\n",
    "df_PM = df_PM.set_index('SEQN')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create labels for opioid users\n",
    "opioid_user_set = set(df_PM.index.tolist())\n",
    "# Following the labeling scheme, we get the label in the opioid table. 0: user, 1: active user, 2: recovered_user\n",
    "df_opioid = df_demo.copy()\n",
    "df_opioid['opioid_label'] = 0\n",
    "df_opioid['opioid_label'] = df_opioid.index.map(df_DU['active_user']).fillna(0)\n",
    "df_opioid.loc[df_opioid.index.isin(opioid_user_set), 'opioid_label'] = 1\n",
    "\n",
    "df_opioid['opioid_label'].loc[df_opioid['opioid_label'] == 2] = 0\n",
    "df_opioid = df_opioid[df_opioid['opioid_label'] == 1]\n",
    "df_opioid[['low_sugar', 'high_protein', 'high_fiber']] = 1\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_main = merge_with_or(df_main, df_opioid[['low_sugar', 'high_protein', 'high_fiber']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.6. Diabetes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "type_table = 'laboratory'\n",
    "df_glucose_1 = concat_data_across_years(type_table, 'BIOPRO',\n",
    "                                ['0506', '0708', '0910', '1112', '1314', '1516', '1718', '1720'], 'D')\n",
    "df_glucose_2 = concat_data_across_years(type_table, 'L40',\n",
    "                                ['0304'], 'C')\n",
    "df_glucose = pd.concat([df_glucose_1, df_glucose_2])[['SEQN', 'LBDSGLSI']]\n",
    "\n",
    "df_ghb_1 = concat_data_across_years(type_table, 'GHB',\n",
    "                                  ['0506', '0708', '0910', '1112', '1314', '1516', '1718', '1720'], 'D')\n",
    "df_ghb_2 = concat_data_across_years(type_table, 'L10',\n",
    "                                  ['0304'], 'C')\n",
    "df_ghb = pd.concat([df_ghb_1, df_ghb_2])[['SEQN', 'LBXGH']]\n",
    "\n",
    "df_diabete = df_glucose.merge(df_ghb, on='SEQN', how='outer')\n",
    "df_diabete['SEQN'] = df_diabete['SEQN'].astype(int).astype(str)\n",
    "df_diabete.set_index('SEQN', inplace=True)\n",
    "\n",
    "def tag_diabetes(row):\n",
    "    return 1 if (row['LBDSGLSI'] >= 7.0) & (row['LBXGH'] >= 6.5) else 0\n",
    "\n",
    "df_diabete['low_sugar'] = df_diabete.apply(tag_diabetes, axis=1, result_type='expand')\n",
    "df_diabete['high_fiber'] = df_diabete.apply(tag_diabetes, axis=1, result_type='expand')\n",
    "df_main = merge_with_or(df_main, df_diabete[['low_sugar', 'high_fiber']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.7. Red Blood Cell Count & Hemoglobin"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "type_table = 'laboratory'\n",
    "df_blood_1 = concat_data_across_years(type_table, 'CBC',\n",
    "                                ['0506', '0708', '0910', '1112', '1314', '1516', '1718', '1720'], 'D')\n",
    "df_blood_2 = concat_data_across_years(type_table, 'L25',\n",
    "                                ['0304'], 'C')\n",
    "df_blood = pd.concat([df_blood_1, df_blood_2])[['SEQN', 'LBXRBCSI', 'LBXHGB']]\n",
    "df_blood['SEQN'] = df_blood['SEQN'].astype(int).astype(str)\n",
    "df_blood.set_index('SEQN', inplace=True)\n",
    "df_blood = df_blood.merge(df_demo, left_index=True, right_index=True, how='left')\n",
    "\n",
    "def tag_RBC(row):\n",
    "    low_threshold_male, low_threshold_female = 13.2, 11.6\n",
    "    threshold = low_threshold_male if row['gender'] == 1 else low_threshold_female\n",
    "    return 1 if (row['LBXRBCSI'] <= 4) & (row['LBXHGB'] < threshold) else 0\n",
    "\n",
    "df_blood[['high_iron', 'high_vitamin_c', 'high_folate_acid', 'high_vitamin_b12']] = df_blood.apply(lambda row: [tag_RBC(row)] * 4, axis=1, result_type='expand')\n",
    "df_main = merge_with_or(df_main, df_blood[['high_iron', 'high_vitamin_c','high_folate_acid', 'high_vitamin_b12']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.8. Osteoporosis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "type_table = 'questionnaire'\n",
    "# Note that we don't actually have data for 11-12 and 15-16. \n",
    "df_ost = concat_data_across_years(type_table, 'OSQ', ['0304', '0506', '0708', '0910', '1112', '1314', '1516', '1718', '1720'], 'C')\n",
    "df_ost = df_ost.drop_duplicates()\n",
    "df_ost['SEQN'] = df_ost['SEQN'].astype(int).astype(str)\n",
    "df_ost = df_ost.set_index('SEQN')\n",
    "df_ost = df_ost[df_ost['OSQ060'] == 1]\n",
    "df_ost[['high_calcium', 'high_vitamin_d', 'high_vitamin_c']] = 1\n",
    "df_main = merge_with_or(df_main, df_ost[['high_calcium', 'high_vitamin_d', 'high_vitamin_c']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_main['tag_num'] = df_main[column_tag].sum(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "tag_num\n",
       "0     34798\n",
       "1     28697\n",
       "3     11063\n",
       "2      6760\n",
       "4      5301\n",
       "5      3777\n",
       "6      3482\n",
       "7      1261\n",
       "8       625\n",
       "9       432\n",
       "10      222\n",
       "11       64\n",
       "12       24\n",
       "13       10\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_main['tag_num'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>gender</th>\n",
       "      <th>age</th>\n",
       "      <th>race</th>\n",
       "      <th>household_income</th>\n",
       "      <th>education</th>\n",
       "      <th>years</th>\n",
       "      <th>age_group</th>\n",
       "      <th>low_carb</th>\n",
       "      <th>low_phosphorus</th>\n",
       "      <th>low_calorie</th>\n",
       "      <th>...</th>\n",
       "      <th>high_protein</th>\n",
       "      <th>low_sugar</th>\n",
       "      <th>high_fiber</th>\n",
       "      <th>high_iron</th>\n",
       "      <th>high_folate_acid</th>\n",
       "      <th>high_vitamin_b12</th>\n",
       "      <th>high_calcium</th>\n",
       "      <th>high_vitamin_d</th>\n",
       "      <th>high_vitamin_c</th>\n",
       "      <th>tag_num</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SEQN</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>100000</th>\n",
       "      <td>2</td>\n",
       "      <td>21</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>1718</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100001</th>\n",
       "      <td>1</td>\n",
       "      <td>39</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>1718</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100002</th>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1718</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100003</th>\n",
       "      <td>2</td>\n",
       "      <td>19</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1718</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100004</th>\n",
       "      <td>2</td>\n",
       "      <td>65</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1718</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99995</th>\n",
       "      <td>2</td>\n",
       "      <td>14</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1718</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99996</th>\n",
       "      <td>2</td>\n",
       "      <td>51</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>1718</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99997</th>\n",
       "      <td>2</td>\n",
       "      <td>64</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1718</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99998</th>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1718</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99999</th>\n",
       "      <td>1</td>\n",
       "      <td>25</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>1718</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>96516 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        gender  age  race  household_income  education years  age_group  \\\n",
       "SEQN                                                                      \n",
       "100000       2   21     4                 0          4  1718          3   \n",
       "100001       1   39     3                 0          4  1718          4   \n",
       "100002       2    9     4                 0          0  1718          1   \n",
       "100003       2   19     2                 0          0  1718          2   \n",
       "100004       2   65     2                 0          1  1718          7   \n",
       "...        ...  ...   ...               ...        ...   ...        ...   \n",
       "99995        2   14     4                 0          0  1718          2   \n",
       "99996        2   51     4                 0          4  1718          6   \n",
       "99997        2   64     1                 0          2  1718          7   \n",
       "99998        2   10     1                 0          0  1718          1   \n",
       "99999        1   25     1                 0          4  1718          3   \n",
       "\n",
       "        low_carb  low_phosphorus  low_calorie  ...  high_protein  low_sugar  \\\n",
       "SEQN                                           ...                            \n",
       "100000         0               0            1  ...             0          0   \n",
       "100001         0               0            1  ...             0          0   \n",
       "100002         0               0            0  ...             0          0   \n",
       "100003         0               0            0  ...             0          0   \n",
       "100004         0               0            0  ...             0          0   \n",
       "...          ...             ...          ...  ...           ...        ...   \n",
       "99995          0               0            0  ...             0          0   \n",
       "99996          0               0            1  ...             0          1   \n",
       "99997          0               0            1  ...             0          0   \n",
       "99998          0               0            0  ...             0          0   \n",
       "99999          0               0            0  ...             0          0   \n",
       "\n",
       "        high_fiber  high_iron  high_folate_acid  high_vitamin_b12  \\\n",
       "SEQN                                                                \n",
       "100000           0          0                 0                 0   \n",
       "100001           0          0                 0                 0   \n",
       "100002           0          0                 0                 0   \n",
       "100003           0          0                 0                 0   \n",
       "100004           0          0                 0                 0   \n",
       "...            ...        ...               ...               ...   \n",
       "99995            0          0                 0                 0   \n",
       "99996            1          0                 0                 0   \n",
       "99997            0          1                 1                 1   \n",
       "99998            0          0                 0                 0   \n",
       "99999            0          0                 0                 0   \n",
       "\n",
       "        high_calcium  high_vitamin_d  high_vitamin_c  tag_num  \n",
       "SEQN                                                           \n",
       "100000             0               0               0        1  \n",
       "100001             0               0               0        3  \n",
       "100002             0               0               0        0  \n",
       "100003             0               0               0        0  \n",
       "100004             0               0               0        2  \n",
       "...              ...             ...             ...      ...  \n",
       "99995              0               0               0        0  \n",
       "99996              0               0               0        5  \n",
       "99997              0               0               1        6  \n",
       "99998              0               0               0        1  \n",
       "99999              0               0               0        0  \n",
       "\n",
       "[96516 rows x 26 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_main"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "for column in column_tag:\n",
    "    df_main = df_main.rename(columns={column: 'user_'+column})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_main.to_csv('../processed_data/user_tagging.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "FRS",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.18"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
